﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using V5_DBUtility;
using System.Data.SqlClient;
using V5_Model;
using System.Data;

using System.Data.OleDb;
namespace V5_DAL.SqlServer {
    public class DALGZProductClass {

        #region  BaseMethod
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public string GetMaxId() {
            return DbHelperSQL.GetMaxID("ID", "Mc_ProductType").ToString();
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(Guid ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Mc_ProductType");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier,32)};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public void Add(ModelGZProductClass model) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Mc_ProductType(");
            strSql.Append("ID,ParentTypeID,TypeName,IsShow,ClassCode,ClassKeyWord,ClassDescription,Info)");
            strSql.Append(" values (");
            strSql.Append("@ID,@ParentTypeID,@TypeName,@IsShow,@ClassCode,@ClassKeyWord,@ClassDescription,@Info)");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier,32),
					new SqlParameter("@ParentTypeID", SqlDbType.UniqueIdentifier,32),
					new SqlParameter("@TypeName", SqlDbType.VarChar,200),
					new SqlParameter("@IsShow", SqlDbType.Bit,1),
					//new SqlParameter("@TimeStamp", SqlDbType.Timestamp,8),
					new SqlParameter("@ClassCode", SqlDbType.VarChar,200),
					new SqlParameter("@ClassKeyWord", SqlDbType.VarChar,200),
					new SqlParameter("@ClassDescription", SqlDbType.VarChar,200),
                    new SqlParameter("@Info", SqlDbType.VarChar,200)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.ParentTypeID;
            parameters[2].Value = model.TypeName;
            parameters[3].Value = model.IsShow;
            //parameters[4].Value = DateTime.Now;//model.TimeStamp;
            parameters[4].Value = model.ClassCode;
            parameters[5].Value = model.ClassKeyWord;
            parameters[6].Value = model.ClassDescription;
            parameters[7].Value = model.Info;

            int result = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            #region 开始更新 ClassCode
            string SQLString = string.Empty;
            string leftCode = string.Empty, selfCode = string.Empty, parentCode = string.Empty;
            if (result > 0) {
                if (model.ParentTypeID != null) {
                    parentCode = Convert.ToString("" + DbHelperSQL.GetSingle("Select ClassCode as NewName From Mc_ProductType Where ID='" + model.ParentTypeID + "'"));
                }
                SQLString = @"
                select Classcode as NewName from Mc_ProductType
                where left(Classcode," + parentCode.Length.ToString() + @")='" + parentCode + @"' and Len(ClassCode)=" + Convert.ToString(parentCode.Length + 4) + @"
                order by Classcode desc
            ";
                leftCode = Convert.ToString("" + DbHelperSQL.GetSingle(SQLString));
                if (leftCode.Length > 0) {
                    selfCode = Convert.ToString(Convert.ToInt32(leftCode.Substring(leftCode.Length - 4, 4)) + 1).PadLeft(4, '0');
                }
                else {
                    selfCode = "0001";
                }
                selfCode = parentCode + selfCode;
                result = DbHelperSQL.ExecuteSql("Update Mc_ProductType Set ClassCode='" + selfCode + "' Where ID='" + model.ID+"'");
            }
            #endregion
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(ModelGZProductClass model) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Mc_ProductType set ");
            strSql.Append("TypeName=@TypeName,");
            strSql.Append("IsShow=@IsShow,");
            strSql.Append("ClassKeyWord=@ClassKeyWord,");
            strSql.Append("ClassDescription=@ClassDescription,");
            strSql.Append("Info=@Info");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@TypeName", SqlDbType.VarChar,200),
					new SqlParameter("@IsShow", SqlDbType.Bit,1),
					new SqlParameter("@ClassKeyWord", SqlDbType.VarChar,200),
					new SqlParameter("@ClassDescription", SqlDbType.VarChar,200),
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier,32),
					new SqlParameter("@ParentTypeID", SqlDbType.UniqueIdentifier,32),
                    new SqlParameter("@Info", SqlDbType.VarChar,200)};
            parameters[0].Value = model.TypeName;
            parameters[1].Value = model.IsShow;
            parameters[2].Value = model.ClassKeyWord;
            parameters[3].Value = model.ClassDescription;
            parameters[4].Value = model.ID;
            parameters[5].Value = model.ParentTypeID;
            parameters[6].Value = model.Info;
            int result = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            #region 开始更新
            string SQLString = string.Empty;
            string leftCode = string.Empty, selfCode = string.Empty, parentCode = string.Empty, oldCode = string.Empty, sourceCode = string.Empty;
            SQLString = "Select ClassCode From Mc_ProductType Where ID='" + model.ParentTypeID + "'";
            parentCode = Convert.ToString("" + DbHelperSQL.GetSingle(SQLString));
            SQLString = "Select ClassCode From Mc_ProductType Where ID='" + model.ID+"'";
            oldCode = Convert.ToString("" + DbHelperSQL.GetSingle(SQLString));
            sourceCode = oldCode;
            oldCode = oldCode.Substring(0, oldCode.Length - 4);
            if (oldCode != parentCode) {
                SQLString = @"
                select Classcode as NewName from Mc_ProductType
                where left(Classcode," + parentCode.Length.ToString() + @")='" + parentCode + @"' and Len(ClassCode)=" + Convert.ToString(parentCode.Length + 4) + @"
                order by Classcode desc ";
                leftCode = Convert.ToString("" + DbHelperSQL.GetSingle(SQLString));
                if (leftCode.Length > 0) {
                    selfCode = Convert.ToString(Convert.ToInt32(leftCode.Substring(leftCode.Length - 4, 4)) + 1).PadLeft(4, '0');
                }
                else {
                    selfCode = "0001";
                }
                selfCode = parentCode + selfCode;
                result = DbHelperSQL.ExecuteSql("Update Mc_ProductType Set ClassCode='" + selfCode + "' Where ID='" + model.ID+"'");
                result = DbHelperSQL.ExecuteSql("update Mc_ProductType Set ClassCode='" + selfCode + "'+right(ClassCode,len(ClassCode)-" + sourceCode.Length.ToString() + ") where ClassCode like '" + sourceCode + "%'");
            }
            #endregion
            if (result > 0) {
                return true;
            }
            else {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(Guid ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Mc_ProductType ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = ID;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                return true;
            }
            else {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ModelGZProductClass GetModel(Guid ID) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from Mc_ProductType ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier,32)};
            parameters[0].Value = ID;
            ModelGZProductClass model = new ModelGZProductClass();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0) {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "") {
                    model.ID = new Guid(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ParentTypeID"] != null && ds.Tables[0].Rows[0]["ParentTypeID"].ToString() != "") {
                    model.ParentTypeID = new Guid(ds.Tables[0].Rows[0]["ParentTypeID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TypeName"] != null && ds.Tables[0].Rows[0]["TypeName"].ToString() != "") {
                    model.TypeName = ds.Tables[0].Rows[0]["TypeName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IsShow"] != null && ds.Tables[0].Rows[0]["IsShow"].ToString() != "") {
                    if ((ds.Tables[0].Rows[0]["IsShow"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsShow"].ToString().ToLower() == "true")) {
                        model.IsShow = true;
                    }
                    else {
                        model.IsShow = false;
                    }
                }
                //if (ds.Tables[0].Rows[0]["TimeStamp"] != null && ds.Tables[0].Rows[0]["TimeStamp"].ToString() != "") {
                //    model.TimeStamp = DateTime.Parse(ds.Tables[0].Rows[0]["TimeStamp"].ToString());
                //}
                if (ds.Tables[0].Rows[0]["ClassCode"] != null && ds.Tables[0].Rows[0]["ClassCode"].ToString() != "") {
                    model.ClassCode = ds.Tables[0].Rows[0]["ClassCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ClassKeyWord"] != null && ds.Tables[0].Rows[0]["ClassKeyWord"].ToString() != "") {
                    model.ClassKeyWord = ds.Tables[0].Rows[0]["ClassKeyWord"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ClassDescription"] != null && ds.Tables[0].Rows[0]["ClassDescription"].ToString() != "") {
                    model.ClassDescription = ds.Tables[0].Rows[0]["ClassDescription"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Info"] != null && ds.Tables[0].Rows[0]["Info"].ToString() != "") {
                    model.Info = ds.Tables[0].Rows[0]["Info"].ToString();
                }
                return model;
            }
            else {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM Mc_ProductType ");
            if (strWhere.Trim() != "") {
                strSql.Append(" where  1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion  Method

        #region Mc_ProductType开始
        public bool DeleteList(string IDlist) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Mc_ProductType ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0) {
                return true;
            }
            else {
                return false;
            }
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder) {
            return this.GetList("*", Top, strWhere, filedOrder);
        }

        public DataSet GetList(int PageSize, int PageIndex, string strWhere, string strOrder, ref int RecordCount) {
            return this.GetList("*", PageSize, PageIndex, strWhere, strOrder, ref RecordCount);
        }

        public List<ModelProductClass> GetModelList(string strWhere) {
            DataSet ds = this.GetList(strWhere);
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                return DBCommonHelper.FillModel<ModelProductClass>(ds);
            }
            return null;
        }

        public List<ModelProductClass> GetModelList(int Top, string strWhere, string filedOrder) {
            DataSet ds = this.GetList(Top, strWhere, filedOrder);
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                return DBCommonHelper.FillModel<ModelProductClass>(ds);
            }
            return null;
        }

        public List<ModelProductClass> GetModelList(int PageSize, int PageIndex, string strWhere, string strOrder, ref int RecordCount) {
            DataSet ds = this.GetList(PageSize, PageIndex, strWhere, strOrder, ref RecordCount);
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                return DBCommonHelper.FillModel<ModelProductClass>(ds);
            }
            return null;
        }

        public DataSet GetList(string strFiled, string strWhere) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select " + strFiled + " ");
            strSql.Append(" FROM Mc_ProductType ");
            if (strWhere.Trim() != "") {
                strSql.Append(" where 1=1 " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList(string strFiled, int Top, string strWhere, string filedOrder) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0) {
                strSql.Append(" Top " + Top.ToString());
            }
            strSql.Append(" " + strFiled + " ");
            strSql.Append(" FROM Mc_ProductType ");
            if (strWhere.Trim() != "") {
                strSql.Append(" where 1=1 " + strWhere);
            }
            if (filedOrder.Trim() != "") {
                strSql.Append(" order by " + filedOrder);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetList(string strFiled, int PageSize, int PageIndex, string strWhere, string strOrder, ref int RecordCount) {
            StringBuilder strSqlCount = new StringBuilder();
            strSqlCount.Append("Select Count(*) as ID From Mc_ProductType ");
            if (strWhere.Trim() != "") {
                strSqlCount.Append(" where 1=1 " + strWhere);
            }
            RecordCount = Convert.ToInt32("0" + DbHelperSQL.GetSingle(strSqlCount.ToString()));
            if (PageIndex <= 1) {
                PageIndex = 1;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Top " + PageSize + " " + strFiled + " from Mc_ProductType where 1=1 ");
            if (strWhere.Trim() != "") {
                strSql.Append(" " + strWhere);
            }
            if (PageIndex > 1) {
                strSql.Append(" And ID Not In (Select Top " + ((PageIndex - 1) * PageSize) + " ID From Mc_ProductType Where 1=1 ");
                if (strWhere.Trim() != "") {
                    strSql.Append(" " + strWhere);
                }
                if (!string.IsNullOrEmpty(strOrder)) {
                    strSql.Append(" Order by " + strOrder);
                }
                strSql.Append(")");
            }
            if (!string.IsNullOrEmpty(strOrder)) {
                strSql.Append(" Order by " + strOrder);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion Mc_ProductType结束


        public void MoveClass(int ID, int isUp) {
            string oldCode = string.Empty;
            oldCode = Convert.ToString("" + DbHelperSQL.GetSingle("Select ClassCode From Mc_ProductType Where ID=" + ID));
            string subStr = "substring";
            int codeLen = oldCode.Length;
            if (codeLen > 1) {
                string temp = string.Empty;
                string wStr = "";
                string newStr = "";
                for (int i = 0; i < codeLen; i++)
                    newStr += "-";
                if (codeLen > 4)
                    wStr = " and left(ClassCode," + Convert.ToString(codeLen - 4) + ")='" + oldCode.Substring(0, codeLen - 4) + "'";
                if (isUp == 1)
                    wStr = "select top 1 ClassCode from Mc_ProductType where len(ClassCode)=" + codeLen.ToString() + " and ClassCode<'" + oldCode + "'" + wStr + " order by ClassCode desc ";
                else
                    wStr = "select top 1 ClassCode from Mc_ProductType where len(ClassCode)=" + codeLen.ToString() + " and ClassCode>'" + oldCode + "'" + wStr + " order by ClassCode ";
                temp = Convert.ToString("" + DbHelperSQL.GetSingle(wStr));
                if (temp.Length > 1) {
                    //Move Under Column
                    wStr = "update Mc_ProductType set ClassCode='" + newStr + "'+" + subStr + "(ClassCode," + Convert.ToString(codeLen + 1) + ",len(Classcode)) where left(ClassCode," + codeLen.ToString() + ")='" + temp + "'";
                    DbHelperSQL.ExecuteSql(wStr);
                    //Update Target Column
                    wStr = "update Mc_ProductType set classcode='" + temp + "'+" + subStr + "(classcode," + Convert.ToString(codeLen + 1) + ",len(Classcode)) where left(classcode," + codeLen.ToString() + ")='" + oldCode + "'";
                    DbHelperSQL.ExecuteSql(wStr);
                    //Update Under Column
                    wStr = "update Mc_ProductType set classcode='" + oldCode + "'+" + subStr + "(classcode," + Convert.ToString(newStr.Length + 1) + ",len(classcode)) where left(Classcode," + newStr.Length.ToString() + ")='" + newStr + "'";
                    DbHelperSQL.ExecuteSql(wStr);
                    //以下是更新栏目信息 可以不用添加
                }
            }
        }
    }
}
